/*
 * Created on 09-Mar-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.trade;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;


import pc.sql.value.DateValue;
import pc.sql.value.FloatValue;
import pc.sql.value.IntValue;
import pc.sql.value.DoubleValue;
import pc.sql.value.StringValue;
import pc.system.Record;
import pc.system.Tasks;
import pc.util.*;
import wstrader.task.Role;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class TradeTask extends Role{
	public float Diff =0;
	public TradeTask(Tasks owner, String menu)
	  {
	    super(owner,menu);
	  }

	public TradeTask(Tasks owner)
	  {
	    super(owner);
	  }
	  public String getFieldValueForId(String table_name,String field_name,int id)
	  {
	  	String Grd_name=getName(field_name,id,table_name);
	  	return Grd_name;
	  }
	  
	  public int setCalculateFieldValue(long id,String field_name,String calculate_field1,String calculate_field2,String operator, String Tab_name)
	  {
	    String sql =
	        "UPDATE " + Tab_name + " " +
	        "SET " + field_name + " = (" + calculate_field1 + operator + calculate_field2 + ") " +
	        "WHERE id = " + id;
	    return getSQLCommand().executeUpdate(sql);  	
	  }  
	  
	  
	  public int setFieldValue(long id,String field_name,String field_value, String Tab_name)
	  {
	    String sql =
	        "UPDATE " + Tab_name + " " +
	        "SET " + field_name + " = '" + field_value + "' " +
	        "WHERE id = " + id;
	    return getSQLCommand().executeUpdate(sql);
	  }
	  
	  public float getSum(String FieldName,String condition_field,String Tbl_name, long recid)
	  {
	    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
	    String sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where " + condition_field + " = " + recid;
	    ResultSet rs = getSQLCommand().executeQuery(sql);
	    float val = 0;
	    try {
		  rs.beforeFirst();
		  if (rs.next()==true)
		  {
		  	val = rs.getFloat("name");
		  }
		  else
		  {
		  	val=0;
		  }
	      rs.close();
	    } catch (SQLException e) {
	      System.out.println(e.getMessage());
	    }
	    return val;
	  }   

	  
	  public float getSumPositionReport(String FieldName,String condition_field,String condition_field1,String Tbl_name, java.sql.Date Mth, String Mrk, String operator,String CType)
	  {
	  	String sql ="";
	    float val = 0;
	    if (Tbl_name == null || Tbl_name.length() == 0) return 0;
	    if (CType.compareTo("")==0)
	    {
	    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where " + condition_field + " " + operator + " '" + Mth + "' and " + condition_field1 + " = '" + Mrk + "'";	    	
	    }
	    else
	    {
	    	sql = "SELECT sum(" +  FieldName + ") as name FROM " + Tbl_name + " where " + condition_field + " " + operator + " '" + Mth + "' and " + condition_field1 + " = '" + Mrk + "' and type = '" + CType + "'";
	    }
	    ResultSet rs = getSQLCommand().executeQuery(sql);
    
	    try {
	    	rs.beforeFirst();
		  	if (rs.next()==true)
		  	{
		  		val = rs.getFloat("name");
		  	}
		  	else
		  	{
		  		val=0;
		  		return val;
		  	}
	      rs.close();
	    } catch (SQLException e) {
	      System.out.println(e.getMessage());
	      val =0;
	      return val;
	    }
	    return val;
	  }
	  
	  public long findID(String tab_name,String key_field,long key_value)
	  {
	    String sql =
	        "SELECT id FROM " + tab_name + " " +
	        "WHERE " + key_field + " = '"+key_value+"'";
	    ResultSet rs = getSQLCommand().executeQuery(sql);
	    long id = 0;
	    try {
	      if (rs.first()) id = rs.getLong("id");
	      rs.close();
	    } catch (SQLException e) {
	      System.out.println(e.getMessage());
	    }
	    return id;
	  }
	  
	  public long findID(String tab_name,String key_field,String key_field1,long key_value,String key_value1)
	  {
	    String sql =
	        "SELECT id FROM " + tab_name + " " +
	        "WHERE " + key_field + " = '"+key_value+"' and " + key_field1 + " = '" + key_value1 + "'";
	    ResultSet rs = getSQLCommand().executeQuery(sql);
	    long id = 0;
	    try {
	      if (rs.first()) id = rs.getLong("id");
	      rs.close();
	    } catch (SQLException e) {
	      System.out.println(e.getMessage());
	    }
	    return id;
	  }  
	  
	  public int equateFieldValue(long id,String field_name,String equate_field,String Tab_name)
	  {
	    String sql =
	        "UPDATE " + Tab_name + " " +
	        "SET " + field_name + " = " + equate_field + " " +
	        "WHERE id = " + id;
	    return getSQLCommand().executeUpdate(sql);  	
	  }
	  
	  public int strConditionalEquateFieldValue(long id,String field_name1,String field_name2,String conditional_field,String conditional_value,String equate_field,String Tab_name)
	  {
	  	String sql = 
	  		"SELECT " + conditional_field + " as name from " + Tab_name + " " +
			"WHERE id = " + id;
	  	ResultSet Rs = getSQLCommand().executeQuery(sql);
	  	int Ret=0;
		try {
			if(Rs.first()==true)
			{
			if (Rs.getString("name").compareTo(conditional_value)==0)
			{
			sql =
				"UPDATE " + Tab_name + " " +
				"SET " + field_name1 + " = " + equate_field + " " +
				"WHERE id = " + id;
			}
			else
				{
				sql =
					"UPDATE " + Tab_name + " " +
					"SET " + field_name2 + " = " + equate_field + " " +
					"WHERE id = " + id;
				}
			Ret = getSQLCommand().executeUpdate(sql);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	      	return Ret;
	  }
	  
	  public int calculateFieldValue(long id,String field_name,String calculate_field1,String calculate_field2,String operator,String Tab_name)
	  {
	    String sql =
	        "UPDATE " + Tab_name + " " +
	        "SET " + field_name + " = (" + calculate_field1 + operator + calculate_field2 + ") " +
	        "WHERE id = " + id;
	    return getSQLCommand().executeUpdate(sql);  	
	  }  
	  
 

	  
	  public String getName(String FieldName,int recid,String Tab_name)
	  {
	    if (Tab_name == null || Tab_name.length() == 0) return "";
	    String sql = "SELECT " +  FieldName + " as name FROM " + Tab_name + " where id = " + recid;
	    ResultSet rs = getSQLCommand().executeQuery(sql);
	    String name = "";
	    try {
	      if (rs.first()) 
	      {
	      	name = rs.getString("name");
	      }
	      else
	      {
	      	name = "-";
	      }
	      rs.close();
	    } catch (SQLException e) {
	    	name ="-";
	      System.out.println(e.getMessage());
	    }
	    return name;
	  }	 
	  
	  public float getValue(String FieldName,int recid,String Tab_name)
	  {
	    if (Tab_name == null || Tab_name.length() == 0) return 0;
	    String sql = "SELECT " +  FieldName + " as value FROM " + Tab_name + " where id = " + recid;
	    ResultSet rs = getSQLCommand().executeQuery(sql);
	    float value = 0;
	    try {
	      if (rs.first()) 
	      {
	      	value = rs.getFloat("value");
	      }
	      else
	      {
	      	value = 0;
	      }
	      rs.close();
	    } catch (SQLException e) {
	    	value =0;
	      System.out.println(e.getMessage());
	    }
	    return value;
	  }	  
	  
	  public ResultSet getConditionalList(String field_name,String condition_field,String condition,String Tab_name)
	  {
	  	if (condition_field.compareTo("All")!=0)
	  	{
	    String sql =
	        "SELECT id," + field_name + " AS name " +
	        "FROM " + Tab_name + " " +
	        "WHERE " + condition_field + " = ?" + " " + 
	        "ORDER BY id desc";

	    Vector vp = new Vector();

	    vp.addElement(new StringValue(condition));
	    return this.getSQLCommand().executeQuery(sql,vp);
	  	}
	  	else
	  	{
		    String sql =
		        "SELECT id," + field_name + " AS name " +
		        "FROM " + Tab_name + " " +
		        "ORDER BY id desc";
		    return this.getSQLCommand().executeQuery(sql);
	  	}
	  }	
	  
	  public ResultSet getConditionalDistinctList(String field_name,String condition_field,String condition,String Tab_name)
	  {
	  	if (condition_field.compareTo("All")!=0)
	  	{
	    String sql =
	        "SELECT distinct " + field_name + " AS name " +
	        "FROM " + Tab_name + " " +
	        "WHERE " + condition_field + " = ?" + " " + 
	        "ORDER BY " + field_name + " DESC";
	    
	    Vector vp = new Vector();

	    vp.addElement(new StringValue(condition));
	    return this.getSQLCommand().executeQuery(sql,vp);
	  	}
	  	else
	  	{
		    String sql =
		        "SELECT distinct " + field_name + " AS name " +
		        "FROM " + Tab_name + " " +
		        "ORDER BY " + field_name + " DESC";
		    
		    return this.getSQLCommand().executeQuery(sql);
	  	}
	  }		  
	  
	  public ResultSet GetTerminalMthList()
	  {
	  	updateTerMth();
	  	String sql = "SELECT DISTINCT month FROM terminal_month_master order by month";
	  	return this.getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet GetTerminalMthList(String type)
	  {
	  	updateTerMth();
	  	String sql = "SELECT month FROM terminal_month_master where type = '" + type + "' order by month";
	  	return this.getSQLCommand().executeQuery(sql);
	  }
	  
	  private void updateTerMth()
	  {
	  	
	  	ResultSet rs = null;
	  	String Sql = "select * from terminal_month_master where type = 'NYC' order by month";
	  	String Correct_Month = "";
	  	java.sql.Date New_Month =null;
	  	java.sql.Date ChkMth = null;
	  	
	  	ChkMth = Utils.strToDate("01-" + Utils.dateToStr(Utils.today()).substring(3));
	  	
	  	rs = getSQLCommand().executeQuery(Sql);
	  	try {
			rs.beforeFirst();
		  	
		  	while (rs.next())
		  	{
		  		if (rs.getDate("month").before(ChkMth)==true)
		  		{
		  			String yrs = Utils.today().toString();
		  			int int_yrs = Utils.strToInt(yrs.substring(0,4))+1;
		  			String Correct_Year = Utils.numberToStr(int_yrs,0).substring(3);
		  			String Cur_mth = (Utils.dateToStr(rs.getDate("month")).substring(3)).substring(0,3);
		  			Correct_Month = "01-" + Cur_mth + "-" + Correct_Year;
		  			New_Month = Utils.strToDate(Correct_Month);
		  			int i = getSQLCommand().executeUpdate("update terminal_month_master set month = '" + New_Month + "' where month = '" + rs.getDate("month") + "'");
		  		}
		  	}
		  	rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	  	Sql = "select * from terminal_month_master where type = 'LIFFE' order by month";
	  	Correct_Month = "";
	  	New_Month =null;
	  	rs = getSQLCommand().executeQuery(Sql);
	  	try {
			rs.beforeFirst();
		  	
		  	while (rs.next())
		  	{
		  		if (rs.getDate("month").before(ChkMth)==true)
		  		{
		  			String yrs = Utils.today().toString();
		  			int int_yrs = Utils.strToInt(yrs.substring(0,4))+1;
		  			String Correct_Year = Utils.numberToStr(int_yrs,0).substring(3);
		  			String Cur_mth = (Utils.dateToStr(rs.getDate("month")).substring(3)).substring(0,3);
		  			Correct_Month = "01-" + Cur_mth + "-" + Correct_Year;
		  			New_Month = Utils.strToDate(Correct_Month);
		  			int i = getSQLCommand().executeUpdate("update terminal_month_master set month = '" + New_Month + "' where month = '" + rs.getDate("month") + "'");
		  		}
		  	}	
		  	rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	  }
	  
	  public void updatedPosRptField(long pcnt_id)
	  {
	  	String Sql = "";
	  	ResultSet prs = null;
	  	String cnt_type ="";
	  	float cnttons =0;
	  	float fixtons = 0;
	  	float unfixton =0;
	  	float pendingtons =0;
		float receivetons =0;
		int i =0;
		Sql = "select tons,contract_price_type,pending_tons,receive_tons,fixed_tons,unfixed_tons from contracts where id = " + pcnt_id;
		prs = getSQLCommand().executeQuery(Sql);

	  	try 
		{
			prs.beforeFirst();
	  		prs.next();
	  		cnttons = prs.getFloat("tons");
	  		cnt_type =prs.getString("contract_price_type");
	  		fixtons = prs.getFloat("fixed_tons");
	  		unfixton =prs.getFloat("unfixed_tons");
	  		pendingtons =prs.getFloat("pending_tons");
	  		receivetons =prs.getFloat("receive_tons");
	  		if (cnt_type.compareTo("U")==0)
	  		{
	  			if ((unfixton == cnttons) && (pendingtons == cnttons))
				{
	  				
	  				i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = " + cnttons + " where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = 0 where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);
				}
	  			
	  			if ((unfixton == cnttons) && (pendingtons != cnttons))
				{
	  				
	  				i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = " + pendingtons + " where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = 0 where id = " + pcnt_id);
	  				i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = " + receivetons + " where id = " + pcnt_id);
				}	
	  			
	  			if ((unfixton != cnttons) && (pendingtons != cnttons))
				{
	  				
	  				if (fixtons == receivetons)
	  				{
	  					System.out.println("loop3-1");
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = " + pendingtons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + receivetons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);
	  				}
	  				
	  				if (fixtons > receivetons)
	  				{
	  					
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = fixed_tons - receive_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = unfixed_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + receivetons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);
	  				}	  				
	  				
	  				if (fixtons < receivetons)
	  				{
	  					
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = pending_tons - pending_fix_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + fixtons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = receive_tons - receive_fix_tons where id = " + pcnt_id);
	  				}	  					  				
				}	  			
	  			
	  			if ((unfixton != cnttons) && (pendingtons == cnttons))
				{
	  				
	  				if (fixtons == receivetons)
	  				{
	  					
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = " + pendingtons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + receivetons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);
	  				}
	  				
	  				if (fixtons > receivetons)
	  				{
	  					
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = fixed_tons - receive_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = unfixed_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + receivetons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);
	  				}	  				
	  				
	  				if (fixtons < receivetons)
	  				{
	  					
	  					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = 0 where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = pending_tons - pending_fix_tons where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + fixtons + " where id = " + pcnt_id);
	  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = receive_tons - receive_fix_tons where id = " + pcnt_id);
	  				}	  					  				
				}	  				  			
	  		}
	  		else
	  		{
	  			
					i = getSQLCommand().executeUpdate("update contracts set pending_fix_tons = " + pendingtons + " where id = " + pcnt_id);
  					i = getSQLCommand().executeUpdate("update contracts set pending_unfix_tons = 0 where id = " + pcnt_id);
  					i = getSQLCommand().executeUpdate("update contracts set receive_fix_tons = " + receivetons + " where id = " + pcnt_id);
  					i = getSQLCommand().executeUpdate("update contracts set receive_unfix_tons = 0 where id = " + pcnt_id);	  			
	  		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	  }
	  
	  public ResultSet GetBasisPrice()
	  {
	  	String StrSDt =getRequest().getStringValue("shipment_last_date");
	  	java.sql.Date SDt=new java.sql.Date(0);
	  	if (StrSDt.length()>0)
	  	{
	  		SDt = Utils.strToDate("01-" + StrSDt.substring(3));
	  	}
	  	java.sql.Date TDt = Utils.strToDate(getRequest().getStringValue("terminal_month"));
	  	java.sql.Date DbDt1	= Utils.strToDate(getRequest().getStringValue("daily_basis_date"));
	  	java.sql.Date DbDt = Utils.previousDate(DbDt1);
	  	
	  	String TerMrk = getRequest().getStringValue("terminal_market");

	  	ResultSet Rs = getSQLCommand().executeQuery("select month from terminal_month_master where type = '" + TerMrk + "' and month > '" + SDt + "' order by month");

	  	try {
			Rs.beforeFirst();
			if (Rs.next()==true)
			{
//				Rs.next();
				if (SDt==TDt)
				{
					if (Rs.next()==true)
					{
						Rs.next();
						TDt = Rs.getDate("Month");
					}
					else
					{
						TDt = Rs.getDate("Month");
					}
				}
				else
				{
					TDt = Rs.getDate("Month");
				}
				Rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	  	String sql = "SELECT terminal_month,closing_price,basis_price FROM futures_daily_basis where terminal_month = '" + TDt + "' and terminal_market = '" + TerMrk + "' and daily_basis_date = '" + DbDt + "'";
	  	return this.getSQLCommand().executeQuery(sql);
	  }
	  
	  public double GetExchangeRate()
	  {
	  	ResultSet Rs = getSQLCommand().executeQuery("Select usd_to_vnd from exchange_rate_master order by date desc");
	  	double Rt =0;
	  	try {
			Rs.beforeFirst();
		  	if (Rs.next() == true)
		  	{
		  		Rt = Rs.getDouble("usd_to_vnd");
			  	Rs.close();
		  	}			
		  	else
		  	{
		  		Rt = 0;
		  	}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Rt;
	  }
	  
	  public ResultSet DailyBasisDate()
	  {
	  	return getSQLCommand().executeQuery("select distinct daily_basis_date as name from futures_daily_basis order by daily_basis_date desc");
	  }	  
	  
	  public float GetTerminalPrice()
	  {
	  	java.sql.Date TDt = Utils.strToDate(getRequest().getStringValue("terminal_month"));
	  	java.sql.Date DbDt1 = Utils.strToDate(getRequest().getStringValue("daily_basis_date"));
	  	java.sql.Date DbDt = Utils.previousDate(DbDt1);
	  	
	  	String TerMrk = getRequest().getStringValue("terminal_market");	
	  	Diff = getRequest().getFloatValue("differential");
	  	String sql = "SELECT basis_price FROM futures_daily_basis where terminal_month = '" + TDt + "' and terminal_market = '" + TerMrk + "' and daily_basis_date = '" + DbDt + "'";
	  	ResultSet Rs = getSQLCommand().executeQuery(sql);
	  	float bp = 0;
	  	try {
			Rs.beforeFirst();
		  	if (Rs.next()==true)
		  	{
		  		bp= Rs.getFloat("basis_price");
		  	}
		  	else
		  	{
		  		bp =0;
		  	}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return bp;
	  }
	  
	  public float getDiff()
	  {
	  	return Diff;
	  }
	  
	  public String getNew_FutRef()
	  {
	    String sql =
	        "SELECT MAX(ref_number) as ref_number FROM futures_contract ";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "FC 00-00000";
	    rc.close();
	    return "FC" + " " + this.getNextRefNumber(ref);
	  }	 
	  
	  public ResultSet getBuyerSellerList()
	  {
    	String sql = "SELECT id,name " +
    	    	  "FROM company_master " +
    	    	  "WHERE buyer = 1 or seller=1 " +
    	    	  "ORDER BY name,id";    
	    return this.getSQLCommand().executeQuery(sql);
	  }	  
	
	  public ResultSet getBuyerList()
	  {
    	String sql = "SELECT id,name " +
    	    	  "FROM company_master " +
    	    	  "WHERE buyer = 1 " +
    	    	  "ORDER BY name,id";    
	    return this.getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet getSellerList()
	  {
    	String sql = "SELECT id,name " +
    	    	  "FROM company_master " +
    	    	  "WHERE seller=1 " +
    	    	  "ORDER BY name,id";    
	    return this.getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet getGradeDescList()
	  {
    	String sql = "SELECT id,name as grd,description as name " +
    	    	  "FROM grade_master " +
    	    	  "ORDER BY name";    
	    return this.getSQLCommand().executeQuery(sql);
	  }	
	  
	  public float getReceivedTons(long id)
	  {
	  	float recdtons =0;
	  	String StrSql = "";
  		StrSql = "SELECT sum(weight_note.net_weight) as tons " +
						"FROM weight_note " +
						"INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
						"INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
						"where contracts.id = " + id + " " +
						"and weight_note.type='I'";
	  	ResultSet Rs = getSQLCommand().executeQuery(StrSql);
	  	
	  	try {
			Rs.beforeFirst();
			
			if (Rs.next()==true) 
			{
				recdtons = Rs.getFloat("tons");
			}
			else
			{
				recdtons = 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	
	  	return recdtons;
	  }
	  
	  public float getInvoicedTons(long id)
	  {
	  		float InvTon =0;
	  		String StrSql = "";

	  			StrSql = "SELECT sum(invoice.net_weight) as tons " +
				"FROM invoice " +
				"INNER JOIN shipping_instruction ON (invoice.inst_id=shipping_instruction.id) " +
				"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
				"WHERE (contracts.id = " + id + " )" ;

	  		ResultSet Rs = getSQLCommand().executeQuery(StrSql);

	  		try {
	  			Rs.beforeFirst();

	  			if (Rs.next()==true) 
	  			{
	  				InvTon = Rs.getFloat("tons");
	  			}
	  			else
	  			{
	  				InvTon = 0;
	  			}
	  		} catch (SQLException e) {
	  			// TODO Auto-generated catch block
	  			e.printStackTrace();
	  		}  	
	  	
	  	return InvTon;
	  }  	  
	  
	  public void updateContract()
	  {
	  	long id = getRequest().getLongValue("contract_id");
	  	String CntType = getContractType(id);
	  	if (CntType.compareTo("")==0) return;
	  	
	  	if (CntType.compareTo("P")==0)
	  	{
	  		float sumValue = getReceivedTons(id);
	  		int Ret = setFieldValue(id,"receive_tons"," "+sumValue,"contracts");
	  		Ret = setCalculateFieldValue(id,"pending_tons","tons","receive_tons","-","contracts");
	  		updatedPosRptField(id);
	  	}
	  	if (CntType.compareTo("S")==0)	  		
		{
		  		float sumValue = getInvoicedTons(id);
		  		int Ret = setFieldValue(id,"inv_tons"," "+sumValue,"contracts");
		}
	  
	  }
	  
	  public float getTotStockTon(long Q_id,long G_id)
	  {
	  		float RecdTon =0;
	  		String StrSql = "";
	  	
	  		if (G_id ==0)
	  		{
	  		  StrSql = "SELECT sum(weight_note.stock_weight) AS tons, " +
			  		   "grade_master.quality_id " +
                       "FROM weight_note " +
			  		   "INNER JOIN grade_master ON (weight_note.grade_id=grade_master.id) " +
			  		   "WHERE grade_master.quality_id = " + Q_id + " " +
			  		   "GROUP BY grade_master.quality_id";
	  		}
	  		else
	  		{
		  		  StrSql = "SELECT sum(weight_note.stock_weight) AS tons, " +
		  		   	       "grade_master.quality_id,weight_note.grade_id " +
                           "FROM weight_note " +
				           "INNER JOIN grade_master ON (weight_note.grade_id=grade_master.id) " +
						   "WHERE weight_note.grade_id = " + G_id + " and grade_master.quality_id  = " + Q_id + " " +
				           "GROUP BY grade_master.quality_id,weight_note.grade_id";	  			
	  		}
	  		
	  		ResultSet Rs = getSQLCommand().executeQuery(StrSql);

	  		try {
	  			Rs.beforeFirst();

	  			if (Rs.next()==true) 
	  			{
	  				RecdTon = Rs.getFloat("tons");
	  			}
	  			else
	  			{
	  					RecdTon = 0;
	  			}
	  		} catch (SQLException e) {
	  			// TODO Auto-generated catch block
	  			e.printStackTrace();
	  		}  	
	  	
	  	return RecdTon;
	  }
	  
	  public float getTotRecdTon(long Q_id,long G_id)
	  {
	  		float RecdTon =0;
	  		String StrSql = "";
	  	
	  		if (G_id ==0)
	  		{
	  		 StrSql = "SELECT sum(weight_note.net_weight) as tons " +
			  		 "FROM weight_note " +
					 "INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
					 "INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
					 "WHERE (contracts.quality_id = " + Q_id + ") and weight_note.type='I'" ;
	  		  //StrSql = "SELECT sum(weight_note.stock_weight) AS tons, " +
			  //		   "grade_master.quality_id " +
              //         "FROM weight_note " +
			  //		   "INNER JOIN grade_master ON (weight_note.grade_id=grade_master.id) " +
			  //		   "WHERE grade_master.quality_id = " + Q_id + " " +
			  //		   "GROUP BY grade_master.quality_id";
	  		}
	  		else
	  		{
	  	  		StrSql = "SELECT sum(weight_note.net_weight) as tons " +
				 "FROM weight_note " +
				 "INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
				 "INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
				 "WHERE (contracts.quality_id = " + Q_id + ") " +
				 "AND (contracts.grade_id = " + G_id + ") and weight_note.type='I'";
		  		 // StrSql = "SELECT sum(weight_note.stock_weight) AS tons, " +
		  		 //  	       "grade_master.quality_id,weight_note.grade_id " +
                 //          "FROM weight_note " +
				 //          "INNER JOIN grade_master ON (weight_note.grade_id=grade_master.id) " +
				//		   "WHERE weight_note.grade_id = " + G_id + " and grade_master.quality_id  = " + Q_id + " " +
				//           "GROUP BY grade_master.quality_id,weight_note.grade_id";	  			
	  		}
	  		
	  		ResultSet Rs = getSQLCommand().executeQuery(StrSql);

	  		try {
	  			Rs.beforeFirst();

	  			if (Rs.next()==true) 
	  			{
	  				RecdTon = Rs.getFloat("tons");
	  			}
	  			else
	  			{
	  					RecdTon = 0;
	  			}
	  		} catch (SQLException e) {
	  			// TODO Auto-generated catch block
	  			e.printStackTrace();
	  		}  	
	  	
	  	return RecdTon;
	  }
	  
	  public float getTotInvTon(long Q_id,long G_id)
	  {
	  		float InvTon =0;
	  		String StrSql = "";
	        
	  		if (G_id == 0)
	  		{
//	  			StrSql = "SELECT sum(invoice.net_weight) as tons " +
//				"FROM invoice " +
//				"INNER JOIN shipping_instruction ON (invoice.inst_id=shipping_instruction.id) " +
//				"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
//				"WHERE (contracts.quality_id = " + Q_id + " )" ;
	  			StrSql = "select sum(delivered_tons) as tons " +
				"from shipping_instruction " +
				"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
				"WHERE (contracts.quality_id = " + Q_id + " )";
	  		}
	  		else
	  		{
//	  			StrSql = "SELECT sum(invoice.net_weight) as tons " +
//				"FROM invoice " +
//				"INNER JOIN shipping_instruction ON (invoice.inst_id=shipping_instruction.id) " +
//				"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
//				"WHERE (contracts.quality_id = " + Q_id + " ) " +
//				"AND (contracts.grade_id = " + G_id + ")";
	  			StrSql = "select sum(delivered_tons) as tons " +
				"from shipping_instruction " +
				"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
				"WHERE (contracts.quality_id = " + Q_id + " )"  +
				"AND (contracts.grade_id = " + G_id + ")";	  			
	  		}
	  		ResultSet Rs = getSQLCommand().executeQuery(StrSql);

	  		try {
	  			Rs.beforeFirst();

	  			if (Rs.next()==true) 
	  			{
	  				InvTon = Rs.getFloat("tons");
	  			}
	  			else
	  			{
	  				InvTon = 0;
	  			}
	  		} catch (SQLException e) {
	  			// TODO Auto-generated catch block
	  			e.printStackTrace();
	  		}  	
	  	
	  	return InvTon;
	  }  
	  
	  public float GetStockDiff(long Q_id,long G_id)
	  {
	  	float stkdiff =0;

	  	float tdiff =0;
	  	float tton=0;
	  	
	  	float stk_totdiff =0;
	  	float stk_totton=0;
	  	
	  	float inv_totdiff =0;
	  	float inv_totton=0;
	  	
	  	String StrSql = "";
	  	String StrSqlI ="";
	  	
  		if (G_id ==0)
  		{
  		StrSql = "SELECT sum(weight_note.net_weight*contracts.differential_fob) as totdiff,sum(weight_note.net_weight) as totton " +
				 "FROM weight_note " +
				 "INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
				 "INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
				 "WHERE (contracts.quality_id = " + Q_id + ")" ;
  		}
  		else
  		{
  	  		StrSql = "SELECT sum(weight_note.net_weight*contracts.differential_fob) as totdiff,sum(weight_note.net_weight) as totton " +
			 "FROM weight_note " +
			 "INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
			 "INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
			 "WHERE (contracts.quality_id = " + Q_id + ") " +
			 "AND (contracts.grade_id = " + G_id + ")";  			
  		}
  		
  		//System.out.println(StrSql);
  		
	  	ResultSet Rs = getSQLCommand().executeQuery(StrSql);
	  	
  		if (G_id == 0)
  		{
  			StrSqlI = "SELECT sum(invoice.net_weight) as itotton,sum(invoice.net_weight*contracts.differential_fob) as itotdiff " +
			"FROM invoice " +
			"INNER JOIN shipping_instruction ON (invoice.inst_id=shipping_instruction.id) " +
			"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
			"WHERE (contracts.quality_id = " + Q_id + " )" ;
  		}
  		else
  		{
  			StrSqlI = "SELECT sum(invoice.net_weight) as itotton,sum(invoice.net_weight*contracts.differential_fob) as itotdiff  " +
			"FROM invoice " +
			"INNER JOIN shipping_instruction ON (invoice.inst_id=shipping_instruction.id) " +
			"INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
			"WHERE (contracts.quality_id = " + Q_id + " ) " +
			"AND (contracts.grade_id = " + G_id + ")";  			
  		}
  		
  		//System.out.println(StrSql);
  		
  		ResultSet RsI = getSQLCommand().executeQuery(StrSqlI);
  		
  		try {
  			Rs.beforeFirst();

  			if (Rs.next()==true) 
  			{
  				stk_totdiff = Rs.getFloat("totdiff");
				stk_totton = Rs.getFloat("totton");
  			}
  			else
  			{
  					stk_totdiff = 0;
  					stk_totton = 0;
  			}
  			
  			RsI.beforeFirst();

  			if (RsI.next()==true) 
  			{
  				inv_totdiff = RsI.getFloat("itotdiff");
  				inv_totton = RsI.getFloat("itotton");
  			}
  			else
  			{
  				inv_totdiff = 0;
  				inv_totton = 0;
  			}  			
  			
  			tdiff = stk_totdiff;// - inv_totdiff;
  			tton = stk_totton;// - inv_totton;

  			if (tdiff != 0) 
  			{
  				stkdiff=tdiff/tton;
  			}
  		} catch (SQLException e) {
  			// TODO Auto-generated catch block
  			e.printStackTrace();
  		}  
  		
  		
	  	return stkdiff;
	  }
	  
	  public String getContractType(long id)
	  {
	  		String Type = "";
	  		String StrSql = "";
	  		ResultSet Rs = null;
	  		StrSql = "select type from contracts where id = " + id;
	  		Rs = getSQLCommand().executeQuery(StrSql);
	  		try {
	  			Rs.beforeFirst();

	  			if (Rs.next()==true) 
	  			{
	  				Type = Rs.getString("type");
	  			}
	  			else
	  			{
	  				Type = "";
	  			}
	  		} catch (SQLException e) {
	  			// TODO Auto-generated catch block
	  			e.printStackTrace();
	  		}  	
	  		return Type;
	  }
	  
	  public long getLatestID(String TabName,long CondId)
	  {
	  	long id = 0;
	  	String Sql = "select max(id) as name from " + TabName + " where id = " + CondId;
	  	ResultSet Rs = getSQLCommand().executeQuery(Sql);
	  	try {
	  		
			Rs.beforeFirst();
			if (Rs.next()==true) id = Rs.getLong("name");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	
	  	return id;
	  }
	  
	  public long getCount(String TabName,String field_name,long CondId)
	  {
	  	long id = 0;
	  	String Sql = "select count(*) as name from " + TabName + " where " + field_name + " = " + CondId;
	  	
	  	ResultSet Rs = getSQLCommand().executeQuery(Sql);
	  	try {
	  		
			Rs.beforeFirst();
			if (Rs.next()==true) id = Rs.getLong("name");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	
	  	return id;
	  }	  
	  
	  public void createTerminalPriceRecord(java.sql.Date dt,String Mrk,double Val)
	  {
	  	int year = Utils.getDateYear(dt);
	  	int month = Utils.getDateMonth(dt);
	  	String Yrs = Utils.numberToStr(year,0,false);
	  	System.out.println("Yrs:-" + Yrs.substring(3));
	  	
	  	String FirstMth = null;
	  	String SecondMth = null;
	  	String ThirdMth = null;
	  	String FourthMth = null;
	  	String FifthMth = null;
	  	String SixthMth = null;

	  	int Mth =0;
	  	if (Mrk.compareTo("NYC")==0)
	  	{
	  		if ((month >= 1) && (month <= 3))
	  		{
	  			FirstMth = "Mar-"+ Utils.numberToStr(year,0,false);
	  			SecondMth = "May-"+ Utils.numberToStr(year,0,false);
	  			ThirdMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Sep-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Dec-"+ Utils.numberToStr(year,0,false);
	  			Mth = 1;
	  		}
	  		
	  		if ((month >= 4) && (month <= 5))
	  		{
	  			FirstMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "May-"+ Utils.numberToStr(year,0,false);
	  			ThirdMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Sep-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Dec-"+ Utils.numberToStr(year,0,false);
	  			Mth=2;
	  		}	  	
	  	
	  		if ((month >= 6) && (month <= 7))
	  		{
	  			FirstMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Sep-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Dec-"+ Utils.numberToStr(year,0,false);
	  			Mth = 3;
	  		}	  	
	  	
	  		if ((month >= 8) && (month <= 9))
	  		{
	  			FirstMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "Jul-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Sep-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Dec-"+ Utils.numberToStr(year,0,false);
	  			Mth=4;
	  		}	  	
	  	
	  		if ((month >= 10) && (month <= 12))
	  		{
	  			FirstMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "Jul-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Sep-"+ Utils.numberToStr(year+1,0,false);
	  			FifthMth ="Dec-"+ Utils.numberToStr(year,0,false);
	  			Mth=5;
	  		}
	  	}
	  	
	  	if (Mrk.compareTo("LIFFE")==0)
	  	{
	  		if (month <= 1)
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=1;
	  		}
	  		
	  		if ((month >= 2) && (month <= 3))
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=2;
	  		}	  	
	  	
	  		if ((month >= 4) && (month <= 5))
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=3;
	  		}	  	
	  	
	  		if ((month >= 6) && (month <= 7))
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=4;
	  		}	  	
	  	
	  		if ((month >= 8) && (month <= 9))
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year+1,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=5;
	  		}

	  		if ((month >= 10) && (month <= 11))
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year+1,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year+1,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year,0,false);
	  			Mth=6;
	  		}	
	  		
	  		if (month >= 12)
	  		{
	  			FirstMth = "Jan-"+ Utils.numberToStr(year+1,0,false);
	  			SecondMth = "Mar-"+ Utils.numberToStr(year+1,0,false);
	  			ThirdMth = "May-"+ Utils.numberToStr(year+1,0,false);
	  			FourthMth = "Jul-"+ Utils.numberToStr(year+1,0,false);
	  			FifthMth ="Sep-"+ Utils.numberToStr(year+1,0,false);
	  			SixthMth ="Nov-"+ Utils.numberToStr(year+1,0,false);
	  			Mth=1;
	  		}	
	  		
	  		System.out.println("Ter1:-" + FirstMth);
	  		System.out.println("Ter2:-" + SecondMth);
	  		System.out.println("Ter3:-" + ThirdMth);
	  		System.out.println("Ter4:-" + FourthMth);
	  		System.out.println("Ter5:-" + FifthMth);
	  		System.out.println("Ter6:-" + SixthMth);
	  		
	  	}
	  	
	  	int dLoop=1;
	  	if (Mrk.compareTo("NYC")==0)
	  	{
	  		while (dLoop <= 5)
	  		{
	  			String SqlStr ="insert futures_daily_basis " +
				"SET daily_basis_date = ?, " +
				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ?, " +
				"active = ?";
	  			Vector vp = new Vector();
	  			vp.addElement(new DateValue(dt));
	  			vp.addElement(new StringValue(Mrk));
	  			
	  			if (dLoop==1) vp.addElement(new DateValue(Utils.strToDate("01-"+FirstMth)));
	  			if (dLoop==2) vp.addElement(new DateValue(Utils.strToDate("01-"+SecondMth)));
	  			if (dLoop==3) vp.addElement(new DateValue(Utils.strToDate("01-"+ThirdMth)));
	  			if (dLoop==4) vp.addElement(new DateValue(Utils.strToDate("01-"+FourthMth)));
	  			if (dLoop==5) vp.addElement(new DateValue(Utils.strToDate("01-"+FifthMth)));
	  			
	  			if (dLoop==Mth)
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			else
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			
	  			vp.addElement(new FloatValue(0));
	  			vp.addElement(new FloatValue(0));
	  			
	  			if (dLoop==Mth)
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			else
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			
	  			vp.addElement(new IntValue(1));

	  			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
	  			dLoop= dLoop+1;
	  		}	
	  	}
	  	
	  	dLoop=1;
	  	if (Mrk.compareTo("LIFFE")==0)
	  	{
	  		while (dLoop <= 6)
	  		{
	  			String SqlStr ="insert futures_daily_basis " +
				"SET daily_basis_date = ?, " +
				"terminal_market = ?, " +
				"terminal_month = ?, " +
				"closing_price = ?, " +
				"low_price = ?, " +
				"high_price = ?, " +
				"basis_price = ?, " +
				"active = ?";
	  			Vector vp = new Vector();
	  			vp.addElement(new DateValue(dt));
	  			vp.addElement(new StringValue(Mrk));
	  			
	  			if (dLoop==1) vp.addElement(new DateValue(Utils.strToDate("01-"+FirstMth)));
	  			if (dLoop==2) vp.addElement(new DateValue(Utils.strToDate("01-"+SecondMth)));
	  			if (dLoop==3) vp.addElement(new DateValue(Utils.strToDate("01-"+ThirdMth)));
	  			if (dLoop==4) vp.addElement(new DateValue(Utils.strToDate("01-"+FourthMth)));
	  			if (dLoop==5) vp.addElement(new DateValue(Utils.strToDate("01-"+FifthMth)));
	  			if (dLoop==6) vp.addElement(new DateValue(Utils.strToDate("01-"+SixthMth)));
	  			
	  			if (dLoop==Mth)
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			else
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			
	  			vp.addElement(new FloatValue(0));
	  			vp.addElement(new FloatValue(0));
	  			
	  			if (dLoop==Mth)
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			else
	  			{
	  				vp.addElement(new DoubleValue(Val));
	  			}
	  			
	  			vp.addElement(new IntValue(1));

	  			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
	  			dLoop= dLoop+1;
	  		}	
	  	}	  	
	  }
	  
	  public String FormatZero(float no)
	  {
	  	String FormatedNo = "-";
	  	//FormatedNo = (no != 0) ? Utils.numberToStr(no,1,false) : "-";	
	  	if (no < 0) FormatedNo = "(" + Utils.numberToStr((no*-1),1,false) + ")";
	  	if (no > 0) FormatedNo = Utils.numberToStr(no,1,false);
	  	if (no == 0) FormatedNo = "-";
	  	return FormatedNo;
	  }
	  
	  public String FormatZero(float no,int dec)
	  {
	  	String FormatedNo = "-";
	  	//FormatedNo = (no != 0) ? Utils.numberToStr(no,dec,false) : "-";
	  	if (no < 0) FormatedNo = "(" + Utils.numberToStr((no*-1),dec,false) + ")";
	  	if (no > 0) FormatedNo = Utils.numberToStr(no,dec,false);
	  	if (no == 0) FormatedNo = "-";
	  	
	  	return FormatedNo;
	  }
	  
	  public String FormatZero(float no,int dec,String ZFormat)
	  {
	  	String FormatedNo = "-";
	  	
	  	//FormatedNo = (no != 0) ? Utils.numberToStr(no,dec,false) : "-";
	  	if (no < 0) FormatedNo = "(" + Utils.numberToStr((no*-1),dec,false) + ")";
	  	if (no > 0) FormatedNo = Utils.numberToStr(no,dec,false);
	  	if (no == 0) FormatedNo = ZFormat;
	  	
	  	return FormatedNo;
	  }	
	  
		public String GenRefNumber(String Type, String Yrs, String Tbl)
		{
			String RefNo ="";
			float id =0;
			try {
				String Sql = "Select MAX(id) as No from " + Tbl ;
				ResultSet Rs = getSQLCommand().executeQuery(Sql);
				if (Rs.next()==true)
				{
					id = Rs.getInt("No")+1;
				}
				
				if (Utils.numberToStr(id,0,false).length()==1) RefNo = Type + " " + Yrs + "-" + "0000" + Utils.numberToStr(id,0,false);
				if (Utils.numberToStr(id,0,false).length()==2) RefNo = Type + " " + Yrs + "-" + "000" + Utils.numberToStr(id,0,false);
				if (Utils.numberToStr(id,0,false).length()==3) RefNo = Type + " " + Yrs + "-" + "00" + Utils.numberToStr(id,0,false);
				if (Utils.numberToStr(id,0,false).length()==4) RefNo = Type + " " + Yrs + "-" + "0" + Utils.numberToStr(id,0,false);
				if (Utils.numberToStr(id,0,false).length()==5) RefNo = Type + " " + Yrs + "-" + Utils.numberToStr(id,0,false);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			return RefNo;
		}	
		
		public void updatePendingReceiveField()
		{
			ResultSet Rs = getSQLCommand().executeQuery("select id from contracts where type = 'P'");
			
			try {
				Rs.beforeFirst();
				if (Rs.next()==true)
				{
					Rs.beforeFirst();
					while (Rs.next())
					{
						
						String SqlDt = "SELECT delivery_instruction.contract_id,sum(weight_note.net_weight) as RecTon " +
						"FROM weight_note INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
						"where delivery_instruction.contract_id = " + Rs.getInt("id") + " " +
						"and weight_note.type = 'I' " +
						" group by `delivery_instruction`.contract_id";

						ResultSet RsT = getSQLCommand().executeQuery(SqlDt);
					
						RsT.beforeFirst();
						if (RsT.next()==true)
						{
						  
						  getSQLCommand().executeUpdate("update contracts set receive_tons = " + (RsT.getFloat("RecTon")) + " where id = " + Rs.getInt("id"));
						  getSQLCommand().executeUpdate("update contracts set pending_tons = tons - " + (RsT.getFloat("RecTon")) + " where id = " + Rs.getInt("id"));
			  
						  updatedPosRptField(Rs.getInt("id"));
						}
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		 
		public void updatePendingReceiveField(long cntid)
		{
			
			try {

						String SqlDt = "SELECT delivery_instruction.contract_id,sum(weight_note.net_weight) as RecTon " +
						"FROM weight_note INNER JOIN delivery_instruction ON (weight_note.inst_id=delivery_instruction.id) " +
						"where delivery_instruction.contract_id = " + cntid + " " +
						"and weight_note.type = 'I' " +
						" group by `delivery_instruction`.contract_id";

						ResultSet RsT = getSQLCommand().executeQuery(SqlDt);
					
						RsT.beforeFirst();
						if (RsT.next()==true)
						{
						  
						  getSQLCommand().executeUpdate("update contracts set receive_tons = " + (RsT.getFloat("RecTon")) + " where id = " + cntid);
						  getSQLCommand().executeUpdate("update contracts set pending_tons = tons - " + (RsT.getFloat("RecTon")) + " where id = " + cntid);
			  
						  updatedPosRptField(cntid);
						}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		
		public void updateInvoiceField()
		{
			ResultSet Rs = getSQLCommand().executeQuery("select id from contracts where type = 'S' and active = 1");
			
			try {
				Rs.beforeFirst();
				if (Rs.next()==true)
				{
					Rs.beforeFirst();
					while (Rs.next())
					{
						
						String SqlDt = "SELECT sum(delivered_tons) as InvTon " +
						"FROM shipping_instruction " +
						"where contract_id = " + Rs.getInt("id") + " and bill_of_lading_no != '' " +
						" group by `shipping_instruction`.contract_id";
					
						ResultSet RsT = getSQLCommand().executeQuery(SqlDt);
					
						RsT.beforeFirst();
						if (RsT.next()==true)
						{
						  	
						  getSQLCommand().executeUpdate("update contracts set inv_tons = " + RsT.getFloat("InvTon") + " where id = " + Rs.getInt("id"));
						  updatedPosRptField(Rs.getInt("id"));
						}
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}	
		
		public float getTotInProcess(int Q_id,int G_id)
		{
			float WIP =0;
			String Sql = "";
			
			if (G_id == 0)
			{
				Sql = "SELECT sum(wnr.net_weight) AS stkwip " +
				"FROM wnr_allocation wna " +
				"LEFT OUTER JOIN processing_instruction po ON (wna.inst_id=po.id) " +
				"LEFT OUTER JOIN weight_note_ref wnr ON (wna.wnr_id=wnr.id) " +
				"INNER JOIN grade_master ON (po.grade_id=grade_master.id) " +
				"WHERE (wna.status = 2) AND " + 
				"(wna.inst_type = 'P') AND " + 
				"(po.status <> 2) AND " + 
				"(grade_master.quality_id = " + Q_id + ")";
			}
			else
			{
				Sql = "SELECT sum(wnr.net_weight) AS stkwip " +
				"FROM wnr_allocation wna " +
				"LEFT OUTER JOIN processing_instruction po ON (wna.inst_id=po.id) " +
				"LEFT OUTER JOIN weight_note_ref wnr ON (wna.wnr_id=wnr.id) " +
				"INNER JOIN grade_master ON (po.grade_id=grade_master.id) " +
				"WHERE (wna.status = 2) AND " + 
				"(wna.inst_type = 'P') AND " + 
				"(po.status <> 2) AND " + 
				"(po.grade_id = " + G_id + ") AND " +
				"(grade_master.quality_id = " + Q_id + ")";				
			}
			ResultSet Rs = getSQLCommand().executeQuery(Sql);
			try {
				Rs.beforeFirst();
				
				if (Rs.next()==true)
				{
					WIP = Rs.getFloat("stkwip");
				}
				else
				{
					WIP = 0;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return WIP;
		}
		
		
		public float getTotOutProcess(int Q_id, int G_id)
		{
			float WIP =0;
			String Sql ="";
			if (G_id ==0)
			{	
				Sql = "SELECT sum(net_weight) AS stkwop " +
				"FROM weight_note " +
				"INNER JOIN processing_instruction ON (weight_note.inst_id=processing_instruction.id) " +
				"INNER JOIN grade_master ON (processing_instruction.grade_id=grade_master.id) " +
				"WHERE (weight_note.type = 'X') AND " + 
				"(processing_instruction.status = 1) AND " + 
				"(grade_master.quality_id = " + Q_id + ")";
			}
			else
			{
				Sql = "SELECT sum(net_weight) AS stkwop " +
				"FROM weight_note " +
				"INNER JOIN processing_instruction ON (weight_note.inst_id=processing_instruction.id) " +
				"INNER JOIN grade_master ON (processing_instruction.grade_id=grade_master.id) " +
				"WHERE (weight_note.type = 'X') AND " + 
				"(processing_instruction.status = 1) AND " + 
				"(processing_instruction.grade_id = " + G_id + ") AND " +
				"(grade_master.quality_id = " + Q_id + ")";
			}
			ResultSet Rs = getSQLCommand().executeQuery(Sql);
			try {
				Rs.beforeFirst();
				
				if (Rs.next()==true)
				{
					WIP = Rs.getFloat("stkwop");
				}
				else
				{
					WIP = 0;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return WIP;
		}		
		
		  public ResultSet getContractLsDt()
		  {
		  	return getSQLCommand().executeQuery("select distinct last_date from contracts where last_date IS NOT NULL order by last_date");
		  }	
}
